﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for BuddyAgentDAL
/// </summary>
public class BuddyAgentDAL
{
    private static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["BuddyAgentConnectionString"].ConnectionString);
    private static SqlCommand cmd;
    private static SqlDataAdapter da;
    //private static DataTable dt;

    public BuddyAgentDAL()
    {
        //
        // TODO: Add constructor logic here
        //
    }


    public static DataTable GetParticularListingInfo(int listingId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingInformation";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ListingId", listingId);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetBuddyAgentInfo(int AgentId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetBuddyAgent";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentId", AgentId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetListingAllPhotos(int listingId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingImages";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ListingId", listingId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable setListingInformation(int agentId, string text, string price, string country, string state, string city, string area, string categorey,
        string subCategorey, string secSubCategorey, string reason, string desc, int friendsOnly, int bedroom, int bathroom, int sqft, int carParking,
        string watchman, string address, string pets, int showAddressInListing, string phoneNo)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingInformation";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentId", agentId);
        cmd.Parameters.AddWithValue("@ListingHeader", text);
        cmd.Parameters.AddWithValue("@ListingCategorey", categorey);
        cmd.Parameters.AddWithValue("@ListingSubcategorey", subCategorey);
        cmd.Parameters.AddWithValue("@ListingSecSubcategorey", secSubCategorey);
        cmd.Parameters.AddWithValue("@Country", country);
        cmd.Parameters.AddWithValue("@State", state);
        cmd.Parameters.AddWithValue("@City", city);
        cmd.Parameters.AddWithValue("@Area", area);
        cmd.Parameters.AddWithValue("@Price", price);
        cmd.Parameters.AddWithValue("@Description", desc);
        cmd.Parameters.AddWithValue("@ReasonOfListing", reason);
        cmd.Parameters.AddWithValue("@NoOfBedrooms", bedroom);
        cmd.Parameters.AddWithValue("@NoOfBathrooms", bathroom);
        cmd.Parameters.AddWithValue("@SqFeets", sqft);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@PetsAllowed", pets);
        cmd.Parameters.AddWithValue("@CarParking", carParking);
        cmd.Parameters.AddWithValue("@Watchman", watchman);
        cmd.Parameters.AddWithValue("@FriendsOnly", friendsOnly);
        cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
        cmd.Parameters.AddWithValue("@ShowAddress", showAddressInListing);
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void setListingImages(int listingId, string imgPath)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingImages";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ListingId", listingId);
        cmd.Parameters.AddWithValue("@ListingImage", imgPath);
        cmd.Parameters.AddWithValue("@TransType", 2);

        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }


    public static DataSet searchPatternRental(string posteBy, string subCategorey, string secSubCategorey, int minPrice, int maxPrice, int BR,
        int BA, int carParking, int minSqfts, int maxSqfts, string watchman, string parametes, string pets, int ascOrder, int clientId, string facebookId, int active)
    {

        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_SearchPatternRental";
        cmd.Parameters.AddWithValue("@FacebookId", facebookId);
        cmd.Parameters.AddWithValue("@ClientId", clientId);
        cmd.Parameters.AddWithValue("@Active", active);
        cmd.Parameters.AddWithValue("@AssendingOrder", ascOrder);
        cmd.Parameters.AddWithValue("@PostedBy", posteBy);
        cmd.Parameters.AddWithValue("@SubCategorey", subCategorey);
        cmd.Parameters.AddWithValue("@SecSubCategorey", secSubCategorey);
        cmd.Parameters.AddWithValue("@MinPrice", minPrice);
        cmd.Parameters.AddWithValue("@MaxPrice", maxPrice);
        cmd.Parameters.AddWithValue("@Bedroom", BR);
        cmd.Parameters.AddWithValue("@Bathroom", BA);
        cmd.Parameters.AddWithValue("@CarParking", carParking);
        cmd.Parameters.AddWithValue("@MinSqft", minSqfts);
        cmd.Parameters.AddWithValue("@MaxSqft", maxSqfts);
        cmd.Parameters.AddWithValue("@Watchman", watchman);
        cmd.Parameters.AddWithValue("@Pets", pets);
        cmd.Parameters.AddWithValue("@Parameters", parametes);

        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }



    public static DataTable GetCurrentPreviousListing(int AgentId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingInformation";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentId", AgentId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }


    public static DataTable SetBuddyAgentInformation(string fullName, string userImage, string userId, string email)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetBuddyAgent";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentName", fullName);
        cmd.Parameters.AddWithValue("@BuddyAgentImagePath", userImage);
        cmd.Parameters.AddWithValue("@BuddyAgentFacebookId", userId);
        cmd.Parameters.AddWithValue("@BuddyAgentEmailId", email);
        cmd.Parameters.AddWithValue("@TransType", 2);

        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void SetBuddyAgentFriendsInfo(int agentId, int BuddyAgentFriendId, string friendId, string friendName)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetBuddyAgentFriends";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentId", agentId);
        cmd.Parameters.AddWithValue("@BuddyAgentFriendId", BuddyAgentFriendId);
        cmd.Parameters.AddWithValue("@FacebookFriendId", friendId);
        cmd.Parameters.AddWithValue("@FacebookFriendName", friendName);
        cmd.Parameters.AddWithValue("@TransType", 1);

        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataSet GetBuddyAgentInfoBasedOnFBId(string facebookId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetBuddyAgent";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentFacebookId", facebookId);
        cmd.Parameters.AddWithValue("@TransType", 3);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static void updateBuddyAgentfriendId(int agentId, string friendId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetBuddyAgentFriends";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FacebookFriendId", friendId);
        cmd.Parameters.AddWithValue("@BuddyAgentFriendId", agentId);
        cmd.Parameters.AddWithValue("@TransType", 2);

        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable GetCurrentPreviousListingBasedOnFB(string facebookId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingInformation";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BuddyAgentFacebookId", facebookId);
        cmd.Parameters.AddWithValue("@TransType", 4);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void updateListingInformation(int listingId, int agentId, string text, string price, string country, string state, string city,
        string area, string categorey, string subCategorey, string secSubCategorey, string reason, string desc, int friendsOnly, int bedroom, int bathroom,
        int sqft, int carParking, string watchman, string address, string pets, int showAddressInListing, string phoneNo)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingInformation";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ListingId", listingId);
        cmd.Parameters.AddWithValue("@BuddyAgentId", agentId);
        cmd.Parameters.AddWithValue("@ListingHeader", text);
        cmd.Parameters.AddWithValue("@ListingCategorey", categorey);
        cmd.Parameters.AddWithValue("@ListingSubcategorey", subCategorey);
        cmd.Parameters.AddWithValue("@ListingSecSubcategorey", secSubCategorey);
        cmd.Parameters.AddWithValue("@Country", country);
        cmd.Parameters.AddWithValue("@State", state);
        cmd.Parameters.AddWithValue("@City", city);
        cmd.Parameters.AddWithValue("@Area", area);
        cmd.Parameters.AddWithValue("@Price", price);
        cmd.Parameters.AddWithValue("@Description", desc);
        cmd.Parameters.AddWithValue("@ReasonOfListing", reason);
        cmd.Parameters.AddWithValue("@NoOfBedrooms", bedroom);
        cmd.Parameters.AddWithValue("@NoOfBathrooms", bathroom);
        cmd.Parameters.AddWithValue("@SqFeets", sqft);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@PetsAllowed", pets);
        cmd.Parameters.AddWithValue("@CarParking", carParking);
        cmd.Parameters.AddWithValue("@Watchman", watchman);
        cmd.Parameters.AddWithValue("@FriendsOnly", friendsOnly);
        cmd.Parameters.AddWithValue("@PhoneNo", phoneNo);
        cmd.Parameters.AddWithValue("@ShowAddress", showAddressInListing);
        cmd.Parameters.AddWithValue("@TransType", 5);

        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static void deleteAllListingPhotos(string listingId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetListingImages";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ListingId", listingId);
        cmd.Parameters.AddWithValue("@TransType", 3);

        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();


    }

    public static DataTable GetStates()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetStateCityList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetCitiesBasedOnState(string state)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSetStateCityList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@State", state);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
}